package com.tpc.control.bean;

import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

import com.tpc.control.jpa.CommonData;
import com.tpc.control.jpa.CommonDataDAO;
import com.tpc.control.jpa.CommonDataId;
import com.tpc.control.jpa.Company;
import com.tpc.control.jpa.CompanyDAO;
import com.tpc.control.jpa.Division;
import com.tpc.control.jpa.DivisionDAO;
import com.tpc.control.jpa.EntityManagerHelper;
import com.tpc.control.jpa.PlanGreigeDAO;
import com.tpc.control.jpa.Plant;
import com.tpc.control.jpa.PlantColumnDAO;
import com.tpc.control.jpa.PlantDAO;
import com.tpc.control.jpa.RoutingData;
import com.tpc.control.jpa.RoutingDataDAO;
import com.tpc.control.jpa.RoutingDataId;
import com.tpc.control.jpa.SaleData;
import com.tpc.control.jpa.SaleDataDAO;
import com.tpc.control.jpa.SaleDataId;
import com.tpc.control.util.UploadUtil;

public class Con200 {
	private CommonDataDAO commonDataDAO = new CommonDataDAO();
	private RoutingDataDAO routingDataDAO = new RoutingDataDAO();
	private PlantDAO plantDAO = new PlantDAO();
	private PlantColumnDAO plantColumnDAO = new PlantColumnDAO();
	private SaleDataDAO saleDataDAO = new SaleDataDAO();
	private DivisionDAO divisionDAO = new DivisionDAO();
	private PlanGreigeDAO planGreigeDAO = new PlanGreigeDAO();
	private CompanyDAO companyDAO = new CompanyDAO();
	private Timestamp globalTS;
	private CommonData data = new CommonData();
	private RoutingDataId rId = new RoutingDataId();
	private CommonDataId id = new CommonDataId();
	private SaleDataId sId = new SaleDataId();

	private void uploadSaleData() {
		List<Company> companyList = companyDAO.findAll();

		for (int l = 0; l < companyList.size(); l++) {
			if (companyList.get(l).getInactUpload().equals("")) {
				Company company = companyList.get(l);
				company.setOnLoading("X");

				String path = company.getInputFileName();
				List<String[]> list = UploadUtil.readNew(path);
				List<String[]> list2 = UploadUtil.readDateTimeNew(path);

				String date;
				String time;

				String[] lineDate = (String[]) list2.get(0);
				String[] lineTime = (String[]) list2.get(1);

				date = lineDate[3];
				time = lineTime[4];

				String[] sDate = date.split("");

				String yyyyYear = (sDate[7] + sDate[8] + sDate[9] + sDate[10]);
				String mmMonth = (sDate[4] + sDate[5]);
				String ddDate = (sDate[1] + sDate[2]);

				String timestamp = yyyyYear + "-" + mmMonth + "-" + ddDate
						+ " " + time;

				Timestamp ts = Timestamp.valueOf(timestamp);
				Calendar aCalendar = Calendar
						.getInstance(new Locale("th", "US"));
				aCalendar.set(ts.getYear() + 1900, ts.getMonth(), ts.getDate(),
						ts.getHours(), ts.getMinutes(), ts.getSeconds());

				java.util.Date tempdate = aCalendar.getTime();
				Timestamp temp = new Timestamp(tempdate.getTime());

				company.setLastUpdate(temp);

				Calendar calendar = Calendar.getInstance();
				java.util.Date now = calendar.getTime();

				java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(
						now.getTime());
				company.setLastUpload(currentTimestamp);

				globalTS = ts;
				EntityManagerHelper.beginTransaction();
				companyDAO.update(company);
				EntityManagerHelper.commit();
				EntityManagerHelper.closeEntityManager();

				for (int b = 0; b < list.size(); b++) {
					String[] line = (String[]) list.get(b);

					try {
						if (UploadUtil.mySubstringNull(line[1 - 1], 20) != null
								&& UploadUtil.mySubstringNull(line[2 - 1], 6) != null) {
							sId.setSaleOrder((UploadUtil.mySubstringNull(
									line[1 - 1], 20)));
							sId.setSaleItem(UploadUtil.mySubstringNull(
									line[2 - 1], 6));
							SaleData saleData = new SaleData();
							saleData.setId(sId);

							saleData.setCustomer(UploadUtil.mySubstring(
									line[3 - 1], 10));
							saleData.setCustomerName(UploadUtil.mySubstring(
									line[4 - 1], 40));
							saleData.setCustomerPoNo(UploadUtil.mySubstring(
									line[5 - 1], 35));
							saleData.setSaleOrg(UploadUtil.mySubstring(
									line[6 - 1], 4));
							saleData.setDistrChannel(UploadUtil.mySubstring(
									line[7 - 1], 2));

							try {
								Division division = divisionDAO
										.findById(UploadUtil.mySubstring(
												line[8 - 1], 2));
								saleData.setDivision(division);
							} catch (Exception ex) {
							
								ex.printStackTrace();
								saleData.setDivision(null);
							}

							saleData.setPlant(UploadUtil.mySubstring(
									line[10 - 1], 4));
							saleData.setMaterial(UploadUtil.mySubstring(
									line[11 - 1], 18));
							saleData.setDueDate(UploadUtil
									.convertToDate(line[12 - 1]));
							saleData.setOrderAmount(UploadUtil
									.convertToDouble(line[13 - 1]));
							saleData.setCompleteAmount(UploadUtil
									.convertToDouble(line[14 - 1]));
							saleData.setRemainAmount(UploadUtil
									.convertToDouble(line[15 - 1]));
							saleData.setEndUserCode(UploadUtil.mySubstring(
									line[16 - 1], 10));
							saleData.setEndUserName(UploadUtil.mySubstring(
									line[17 - 1], 10));
							saleData.setCompleteFlag(UploadUtil.mySubstring(
									line[18 - 1], 10));

							try {
								Company companyTemp = companyDAO
										.findById(UploadUtil.mySubstring(
												line[19 - 1], 4));
								saleData.setCompany(companyTemp);
							} catch (Exception ex) {
							
								ex.printStackTrace();
								saleData.setCompany(null);
							}
							saleData.setCompany(company);

							saleData.setQuantity(UploadUtil
									.convertToDouble(line[20 - 1]));
							saleData.setUom(UploadUtil.mySubstring(
									line[21 - 1], 6));
							saleData.setPrice(UploadUtil
									.convertToDouble(line[22 - 1]));
							saleData.setCurrency(UploadUtil.mySubstring(
									line[23 - 1], 10));
							try {
								EntityManagerHelper.beginTransaction();

								saleDataDAO.update(saleData);

								EntityManagerHelper.commit();
								EntityManagerHelper.closeEntityManager();
								// data.setSaleData(saleData);
							} catch (Exception ex) {
								EntityManagerHelper.rollback();
								ex.printStackTrace();
								System.out.println("ERROR");
								// data.setSaleData(null);
							}

						} else {
							System.out.println("ERROR");
							// data.setSaleData(null);
						}
					} catch (Exception ex) {
				
						ex.printStackTrace();
						System.out.println("ERROR");
					}
				}

				company.setOnLoading("");
				company.setLastUpdate(globalTS);
				EntityManagerHelper.beginTransaction();
				companyDAO.update(company);
				EntityManagerHelper.commit();
				EntityManagerHelper.closeEntityManager();

			}
		}
	}

	public void gotoCon200() {
		try {
			uploadSaleData();

			List<Plant> plantList = plantDAO.findAll();
			for (int j = 0; j < plantList.size(); j++) {
				if (plantList.get(j).getInactUpload().equals("")) {

					Plant plant = plantList.get(j);
					plant.setOnLoading("X");

					String path = plant.getInputFileName();
					List<String[]> list = UploadUtil.read(path);
					List<String[]> list2 = UploadUtil.readDateTime(path);

					for (int d = 0; d < list2.size(); d++) {
						String[] line2 = (String[]) list2.get(d);

						String rawDate = line2[0];
						String rawTime = line2[17];
						String date = rawDate.split(" ")[2];
						String time = rawTime.split(" ")[2];

						String[] sDate = date.split("");

						String yyyyYear = (sDate[7] + sDate[8] + sDate[9] + sDate[10]);
						String mmMonth = (sDate[4] + sDate[5]);
						String ddDate = (sDate[1] + sDate[2]);

						String timestamp = yyyyYear + "-" + mmMonth + "-"
								+ ddDate + " " + time;
						Timestamp ts = Timestamp.valueOf(timestamp);

						Calendar aCalendar = Calendar.getInstance(new Locale(
								"th", "US"));
						aCalendar.set(ts.getYear() + 1900, ts.getMonth(), ts
								.getDate(), ts.getHours(), ts.getMinutes(), ts
								.getSeconds());

						java.util.Date tempdate = aCalendar.getTime();
						Timestamp temp = new Timestamp(tempdate.getTime());

						plant.setLastUpdate(temp);
						globalTS = temp;
						EntityManagerHelper.beginTransaction();
						plantDAO.update(plant);
						EntityManagerHelper.commit();
			
						EntityManagerHelper.closeEntityManager();
					}

					for (int i = 0; i < list.size(); i++) {

						String[] line = (String[]) list.get(i);

						RoutingData rdata = new RoutingData();
						rId.setProductionNo(UploadUtil.mySubstring(
								line[14 - 1], 12));
						rId.setSeq(UploadUtil.lastChar(line[34 - 1]));
						rdata.setId(rId);

						try {

							rdata.setPlant(plantDAO.findById(line[42 - 1]));

							// System.out.println(line[63-1].toString());
							// System.out.println(UploadUtil.mySubstring(line[63-1],30));

							rdata.setProcessName01(UploadUtil.mySubstring(
									line[62 - 1], 50));
							rdata.setProcess01(UploadUtil.mySubstring(
									line[63 - 1], 30));
							rdata.setProcessName02(UploadUtil.mySubstring(
									line[64 - 1], 50));
							rdata.setProcess02(UploadUtil.mySubstring(
									line[65 - 1], 30));
							rdata.setProcessName03(UploadUtil.mySubstring(
									line[66 - 1], 50));
							rdata.setProcess03(UploadUtil.mySubstring(
									line[67 - 1], 30));
							rdata.setProcessName04(UploadUtil.mySubstring(
									line[68 - 1], 50));
							rdata.setProcess04(UploadUtil.mySubstring(
									line[69 - 1], 30));
							rdata.setProcessName05(UploadUtil.mySubstring(
									line[70 - 1], 50));
							rdata.setProcess05(UploadUtil.mySubstring(
									line[71 - 1], 30));
							rdata.setProcessName06(UploadUtil.mySubstring(
									line[72 - 1], 50));
							rdata.setProcess06(UploadUtil.mySubstring(
									line[73 - 1], 30));
							rdata.setProcessName07(UploadUtil.mySubstring(
									line[74 - 1], 50));
							rdata.setProcess07(UploadUtil.mySubstring(
									line[75 - 1], 30));
							rdata.setProcessName08(UploadUtil.mySubstring(
									line[76 - 1], 50));
							rdata.setProcess08(UploadUtil.mySubstring(
									line[77 - 1], 30));
							rdata.setProcessName09(UploadUtil.mySubstring(
									line[78 - 1], 50));
							rdata.setProcess09(UploadUtil.mySubstring(
									line[79 - 1], 30));
							rdata.setProcessName10(UploadUtil.mySubstring(
									line[80 - 1], 50));
							rdata.setProcess10(UploadUtil.mySubstring(
									line[81 - 1], 30));

							rdata.setProcessName11(UploadUtil.mySubstring(
									line[82 - 1], 50));
							rdata.setProcess11(UploadUtil.mySubstring(
									line[83 - 1], 30));
							rdata.setProcessName12(UploadUtil.mySubstring(
									line[84 - 1], 50));
							rdata.setProcess12(UploadUtil.mySubstring(
									line[85 - 1], 30));
							rdata.setProcessName13(UploadUtil.mySubstring(
									line[86 - 1], 50));
							rdata.setProcess13(UploadUtil.mySubstring(
									line[87 - 1], 30));
							rdata.setProcessName14(UploadUtil.mySubstring(
									line[88 - 1], 50));
							rdata.setProcess14(UploadUtil.mySubstring(
									line[89 - 1], 30));
							rdata.setProcessName15(UploadUtil.mySubstring(
									line[90 - 1], 50));
							rdata.setProcess15(UploadUtil.mySubstring(
									line[91 - 1], 30));
							rdata.setProcessName16(UploadUtil.mySubstring(
									line[92 - 1], 50));
							rdata.setProcess16(UploadUtil.mySubstring(
									line[93 - 1], 30));
							rdata.setProcessName17(UploadUtil.mySubstring(
									line[94 - 1], 50));
							rdata.setProcess17(UploadUtil.mySubstring(
									line[95 - 1], 30));
							rdata.setProcessName18(UploadUtil.mySubstring(
									line[96 - 1], 50));
							rdata.setProcess18(UploadUtil.mySubstring(
									line[97 - 1], 30));
							rdata.setProcessName19(UploadUtil.mySubstring(
									line[98 - 1], 50));
							rdata.setProcess19(UploadUtil.mySubstring(
									line[99 - 1], 30));
							rdata.setProcessName20(UploadUtil.mySubstring(
									line[100 - 1], 50));
							rdata.setProcess20(UploadUtil.mySubstring(
									line[101 - 1], 30));

						} catch (Exception ex) {
						
							//ex.printStackTrace();
						}

						id.setProductionNo(UploadUtil.mySubstring(line[14 - 1],
								12));
						id.setSeq(UploadUtil.lastChar(line[34 - 1]));
						data.setId(id);

						data.setPlant(plantDAO.findById(line[42 - 1]));
						data.setCustomer(UploadUtil
								.mySubstring(line[1 - 1], 10));
						data.setCustomerName(UploadUtil.mySubstring(
								line[2 - 1], 40));
						data.setCustomerPoNo((UploadUtil.mySubstring(
								line[3 - 1], 35)));
						// //
						/*
						 * if(UploadUtil.mySubstringNull(line[4-1],20)!= null &&
						 * UploadUtil.mySubstringNull(line[41-1],20)!=null) {
						 * sId
						 * .setSaleOrder((UploadUtil.mySubstringNull(line[4-1]
						 * ,20)));
						 * sId.setSaleItem(UploadUtil.mySubstringNull(line
						 * [41-1],6)); SaleData saleData = new SaleData();
						 * saleData.setId(sId);
						 * 
						 * try { EntityManagerHelper.beginTransaction();
						 * 
						 * saleDataDAO.update(saleData);
						 * 
						 * EntityManagerHelper.commit();
						 * EntityManagerHelper.closeEntityManager();
						 * data.setSaleData(saleData); } catch(Exception ex) {
						 * data.setSaleData(null); }
						 * 
						 * } else { data.setSaleData(null); }
						 */
						// data.setSaleOrder((UploadUtil.mySubstringNull(line[4-1],20)));
						// //
						// ///
						// data.setSaleItem(UploadUtil.mySubstringNull(line[41-1],6));

						// //
						try {
							if (line[4 - 1] == null
									|| line[4 - 1].length() == 0
									|| line[41 - 1] == null
									|| line[41 - 1].length() == 0) {
								data.setSaleData(null);

							} else {
								sId.setSaleOrder(UploadUtil.mySubstring(
										line[4 - 1], 20));
								sId.setSaleItem(UploadUtil.mySubstring(
										line[41 - 1], 6));
								SaleData saleData = saleDataDAO.findById(sId);
								data.setSaleData(saleData);
							}
						} catch (Exception ex) {
							
							ex.printStackTrace();
							throw new Exception("SaleDataNotFound");
							// data.setSaleData(null);
						}

						data.setDistrChannel((UploadUtil.mySubstring(
								line[5 - 1], 2)));
						data.setSaleCreated(UploadUtil
								.convertToDate(line[33 - 1]));
						data.setPreDue(UploadUtil.convertToDate(line[35 - 1]));
						data.setDueDate(UploadUtil.convertToDate(line[6 - 1]));
						data.setPirNo(UploadUtil.mySubstring(line[12 - 1], 10));
						data.setPirDueDate(UploadUtil
								.convertToDate(line[13 - 1]));
						data.setDivision(UploadUtil
								.mySubstring(line[36 - 1], 2));
						data.setMaterial(UploadUtil
								.mySubstring(line[7 - 1], 18));
						data.setDesignNoFg(UploadUtil.mySubstring(line[8 - 1],
								40));
						data.setDyelotLab(UploadUtil.mySubstring(line[9 - 1],
								50));
						data.setQuantity(UploadUtil
								.convertToDouble(line[10 - 1]));
						data.setUom(UploadUtil.mySubstring(line[11 - 1], 6));
						data.setProdCreateDate(UploadUtil
								.convertToDate(line[15 - 1]));
						data.setPlanStartDate(UploadUtil
								.convertToDate(line[16 - 1]));
						data.setStartDate(UploadUtil
								.convertToDate(line[17 - 1]));
						data.setFinishGoods(UploadUtil
								.convertToDouble(line[24 - 1]));
						data.setLastPostingDate(UploadUtil
								.convertToDate(line[25 - 1]));
						data.setPlanFinishDate(UploadUtil
								.convertToDate(line[26 - 1]));

						data
								.setPoNo((UploadUtil.mySubstring(line[18 - 1],
										40)));
						data.setDesignNoGreige(UploadUtil.mySubstring(
								line[19 - 1], 40));
						data.setGreigeDueDate(UploadUtil
								.convertToDate(line[20 - 1]));
						data.setPlanGreige(UploadUtil.mySubstring(line[21 - 1],
								20));
						data.setActualGreige(UploadUtil
								.convertToDate(line[22 - 1]));
						data.setGreigeRemark(UploadUtil.mySubstring(
								line[23 - 1], 50));
						data
								.setStartDye(UploadUtil
										.convertToDate(line[27 - 1]));
						data.setDyeRemark(UploadUtil.mySubstring(line[38 - 1],
								50));

						data.setSubmitDate(UploadUtil
								.convertToDate(line[28 - 1]));
						data.setDueSubmitDate((UploadUtil.mySubstring(
								line[39 - 1], 20)));
						data.setStatus((UploadUtil
								.mySubstring(line[29 - 1], 20)));
						data.setPlanDueDate(UploadUtil
								.convertToDate(line[30 - 1]));
						data.setCurrentOperation(UploadUtil.mySubstring(
								line[40 - 1], 30));
						data.setUserStatus((UploadUtil.mySubstring(
								line[31 - 1], 30)));
						data.setTotProduceQty(UploadUtil
								.convertToDouble(line[37 - 1]));
						data.setColorStatus(UploadUtil.mySubstring(
								line[43 - 1], 20));

						data.setConfirmToPlan(UploadUtil
								.convertToDate(line[44 - 1]));
						data.setConfirmToSale(UploadUtil
								.convertToDate(line[45 - 1]));
						data.setConfirmType(UploadUtil.lastChar(line[46 - 1]));
						data.setUserStatusCode(UploadUtil.mySubstring(
								line[47 - 1], 20));

						data.setBasicStartDate(UploadUtil
								.convertToDate(line[48 - 1]));
						data.setBasicFinishDate(UploadUtil
								.convertToDate(line[49 - 1]));
						data
								.setOverdueStatus(UploadUtil
										.lastChar(line[50 - 1]));

						try {
						
							
							EntityManagerHelper.beginTransaction();
							CommonData cmData = commonDataDAO.findById(data
									.getId());
							EntityManagerHelper.commit();
							EntityManagerHelper.closeEntityManager();

							data.setReserveDyeRemark(cmData
									.getReserveDyeRemark());
							
						} catch (Exception ex) {
							//ex.printStackTrace();
							data.setReserveDyeRemark(null);
							//EntityManagerHelper.rollback();
							
						}

						try {
							EntityManagerHelper.beginTransaction();
							
								commonDataDAO.update(data);
								routingDataDAO.update(rdata);
							
							
							EntityManagerHelper.commit();
							EntityManagerHelper.closeEntityManager();
						} catch (Exception ex) {
							EntityManagerHelper.rollback();
							
							ex.printStackTrace();
							for (int k = 0; k < line.length; k++) {
								System.out.println("ERROR");
								System.out.print("[" + k + "]" + line[k]);
							}
							
							// i=list.size();
						}
					}
					Calendar calendar = Calendar.getInstance();
					java.util.Date now = calendar.getTime();
					java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(
							now.getTime());
					plant.setLastUpload(currentTimestamp);
					plant.setOnLoading("");
					plant.setLastUpdate(globalTS);
					try
					{
						EntityManagerHelper.beginTransaction();
						plantDAO.update(plant);
						EntityManagerHelper.commit();
						EntityManagerHelper.closeEntityManager();
					}
					catch(Exception ex){
						//EntityManagerHelper.rollback();
						ex.printStackTrace();
					}
					}
			}
		} catch (Exception ex) {
		
			ex.printStackTrace();
		}
	}
}
